package com.atguigu.medicalsugar.mapper;

import com.atguigu.medicalsugar.bean.UserBean;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author lzc
 * @Date 2023/5/29 09:56
 */
public interface UserMapper {
    @Select("select \n" +
        "    date,\n" +
        "    sum(new_user_ct) new_user_ct,\n" +
        "    sum(active_user_ct) active_user_ct,\n" +
        "    sum(new_active_user_ct) new_active_user_ct\n" +
        "from(\n" +
        "    SELECT\n" +
        "        #{date} AS date,\n" +
        "        sum(new_user_ct) AS new_user_ct,\n" +
        "        0 AS active_user_ct,\n" +
        "        0 AS new_active_user_ct\n" +
        "    FROM dws_user_register_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    UNION ALL\n" +
        "    SELECT\n" +
        "        #{date} AS date,\n" +
        "        0 AS new_user_ct,\n" +
        "        sum(active_user_ct) AS active_user_ct,\n" +
        "        0 AS new_active_user_ct\n" +
        "    FROM dws_user_active_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    UNION ALL\n" +
        "    SELECT\n" +
        "        #{date} AS date,\n" +
        "        0 AS new_user_ct,\n" +
        "        0 AS active_user_ct,\n" +
        "        sum(new_active_user_ct) AS new_active_user_ct\n" +
        "    FROM dws_user_new_active_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        ")tmp\n" +
        "group by date")
    List<UserBean> user(Integer date);
}
